﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BaseDal
{
    public class PurchaseGetDal
    {
        private static readonly Lazy<PurchaseGetDal> dal = new Lazy<PurchaseGetDal>(() => new PurchaseGetDal());
        public static PurchaseGetDal Instance { get; } = dal.Value;

        private PurchaseGetDal()
        { }

        private const string tableH = "PurchaseGetHead";

        private const string tableB = "PurchaseGetBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,PG_Number,PG_Date,PG_Person,PG_CompanyId,PG_CompanyName,PG_DeptId,PG_DeptName,");
            sb.Append("PG_OrderID,PG_OrderNo,PG_SupplierId,PG_SupplierName,PG_Remarks,PG_AuditStatus,PG_TotalAmount ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<PurchaseGetHead> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<PurchaseGetHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PurchaseGetHead GetModelById(decimal id)
        {
            // 查询主表
            PurchaseGetHead model;
            StringBuilder sb = GetSelectSql(" and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<PurchaseGetHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetAssetBodyList(model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询子表数据
        /// </summary>
        /// <param name="headID">主表ID</param>
        /// <returns></returns>
        public List<PurchaseGetBody> GetAssetBodyList(decimal headID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,PG_ItemID,PG_ItemName,PG_Specs,PG_Unit,PG_Price,");
            sb.Append("PG_BuyQty,PG_ArrivalQty,PG_Location,PG_TotalCost ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID ");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<PurchaseGetBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddPurchaseGet(PurchaseGetHead model)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("PG_Number,PG_Date,PG_Person,PG_CompanyId,PG_CompanyName,PG_DeptId,PG_DeptName,");
            hSql.Append("PG_OrderID,PG_OrderNo,PG_SupplierId,PG_SupplierName,PG_Remarks,PG_AuditStatus,PG_TotalAmount)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@number,@date,@person,@companyId,@companyName,@deptId,@deptName,");
            hSql.Append("@orderId,@orderNo,@supplierId,@supplierName,@remarks,@auditStatus,@totalAmount)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@number", model.PG_Number);
                    command1.Parameters.AddWithValue("@date", model.PG_Date);
                    command1.Parameters.AddWithValue("@person", model.PG_Person);
                    command1.Parameters.AddWithValue("@companyId", model.PG_CompanyId);
                    command1.Parameters.AddWithValue("@companyName", model.PG_CompanyName);
                    command1.Parameters.AddWithValue("@deptId", model.PG_DeptId);
                    command1.Parameters.AddWithValue("@deptName", model.PG_DeptName);
                    command1.Parameters.AddWithValue("@orderId", model.PG_OrderID);
                    command1.Parameters.AddWithValue("@orderNo", model.PG_OrderNo);
                    command1.Parameters.AddWithValue("@supplierId", model.PG_SupplierId);
                    command1.Parameters.AddWithValue("@supplierName", model.PG_SupplierName);
                    command1.Parameters.AddWithValue("@remarks", model.PG_Remarks);
                    command1.Parameters.AddWithValue("@auditStatus", model.PG_AuditStatus);
                    command1.Parameters.AddWithValue("@totalAmount", model.PG_TotalAmount);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }

                    // 子表数据
                    List<PurchaseGetBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (PurchaseGetBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,PG_ItemID,PG_ItemName,PG_Specs,PG_Unit,PG_Price,PG_BuyQty,PG_ArrivalQty,PG_Location,PG_TotalCost)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@buyQty,@arrivalQty,@location,@totalCost)");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@itemID", itemModel.PG_ItemID);
                                command2.Parameters.AddWithValue("@itemName", itemModel.PG_ItemName);
                                command2.Parameters.AddWithValue("@specs", itemModel.PG_Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.PG_Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.PG_Price);
                                command2.Parameters.AddWithValue("@buyQty", itemModel.PG_BuyQty);
                                command2.Parameters.AddWithValue("@arrivalQty", itemModel.PG_ArrivalQty);
                                command2.Parameters.AddWithValue("@location", itemModel.PG_Location);
                                command2.Parameters.AddWithValue("@totalCost", itemModel.PG_TotalCost);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);
            return true;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdatePurchaseGet(PurchaseGetHead model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableH + " set ");
            strSql.Append("PG_Date=@date,");
            strSql.Append("PG_Person=@person,");
            strSql.Append("PG_CompanyId=@companyId,");
            strSql.Append("PG_CompanyName=@companyName,");
            strSql.Append("PG_DeptId=@deptId,");
            strSql.Append("PG_DeptName=@deptName,");
            strSql.Append("PG_OrderID=@orderId,");
            strSql.Append("PG_OrderNo=@orderNo,");
            strSql.Append("PG_SupplierId=@supplierId,");
            strSql.Append("PG_SupplierName=@supplierName,");
            strSql.Append("PG_Remarks=@remarks,");
            strSql.Append("PG_AuditStatus=@auditStatus,");
            strSql.Append("PG_TotalAmount=@totalAmount ");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@date", model.PG_Date),
                    new SqlParameter("@person", model.PG_Person),
                    new SqlParameter("@companyId", model.PG_CompanyId),
                    new SqlParameter("@companyName", model.PG_CompanyName),
                    new SqlParameter("@deptId", model.PG_DeptId),
                    new SqlParameter("@deptName", model.PG_DeptName),
                    new SqlParameter("@orderId", model.PG_OrderID),
                    new SqlParameter("@orderNo", model.PG_OrderNo),
                    new SqlParameter("@supplierId", model.PG_SupplierId),
                    new SqlParameter("@supplierName", model.PG_SupplierName),
                    new SqlParameter("@remarks", model.PG_Remarks),
                    new SqlParameter("@auditStatus", model.PG_AuditStatus),
                    new SqlParameter("@totalAmount", model.PG_TotalAmount),
                    new SqlParameter("@id", model.ID)
            };
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            //先删除子表数据
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableB + " where HeadID=@headID ");
            SqlParameter[] parameters2 = { new SqlParameter("@headID", model.ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //添加子表数据
            if (model.ItemList != null)
            {
                StringBuilder itemSql;
                foreach (PurchaseGetBody modelt in model.ItemList)
                {
                    itemSql = new StringBuilder();
                    itemSql.Append("insert into " + tableB + "(");
                    itemSql.Append("HeadID,PG_ItemID,PG_ItemName,PG_Specs,PG_Unit,PG_Price,PG_BuyQty,PG_ArrivalQty,PG_Location,PG_TotalCost)");
                    itemSql.Append(" values (");
                    itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@buyQty,@arrivalQty,@location,@totalCost)");
                    SqlParameter[] parameters3 = {
                            new SqlParameter("@headID", model.ID),
                            new SqlParameter("@itemID", modelt.PG_ItemID),
                            new SqlParameter("@itemName", modelt.PG_ItemName),
                            new SqlParameter("@specs", modelt.PG_Specs),
                            new SqlParameter("@unit", modelt.PG_Unit),
                            new SqlParameter("@price", modelt.PG_Price),
                            new SqlParameter("@buyQty", modelt.PG_BuyQty),
                            new SqlParameter("@arrivalQty", modelt.PG_ArrivalQty),
                            new SqlParameter("@location", modelt.PG_Location),
                            new SqlParameter("@totalCost", modelt.PG_TotalCost)
                    };
                    cmd = new CommandInfo(itemSql.ToString(), parameters3);
                    sqllist.Add(cmd);
                }
            }
            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeletePurchaseGet(decimal ID)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 修改审核状态（0：审批中；1：已审批；2：暂存）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatus(int id, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableH + " SET ");
            sb.Append("PG_AuditStatus=@status ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }


        public bool UpdateGetQtyFromRequest(List<PurchaseRequestBody> poBList)
        {
            int rowsAffected = 0;
            if (poBList != null && poBList.Count > 0)
            {
                List<CommandInfo> sqllist = new List<CommandInfo>();
                StringBuilder itemSql;
                foreach (PurchaseRequestBody modelt in poBList)
                {
                    itemSql = new StringBuilder();
                    itemSql.Append("update PurchaseRequestBody set ");
                    itemSql.Append("PR_GetQty=PR_GetQty+" + modelt.PR_GetQty);
                    itemSql.Append(" where PR_ItemID='" + modelt.PR_ItemID + "' and HeadID=" + modelt.HeadID);

                    CommandInfo cmd = new CommandInfo(itemSql.ToString());
                    sqllist.Add(cmd);
                }
                rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            }
            return rowsAffected > 0;
        }

    }
}